package com.example.sqlitetest_test;

import java.util.HashMap;
import java.util.Map;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.test.AndroidTestCase;
import android.util.Log;

import com.example.performance.SQLiteHepler;

@SuppressWarnings("unused")
public class PerformanceTest extends AndroidTestCase {
	public static final String TABLE_NAME = "my_table";

	public static final String UID = "_id";
	public static final String NAME = "name";
	public static final String SCORE = "score";
	SQLiteHepler sqh;

	@Override
	protected void setUp() throws Exception {
		sqh = new SQLiteHepler(getContext());
		SQLiteDatabase sqdb = sqh.getWritableDatabase();

		// queryForSQL(sqdb);
		// queryForJava(sqdb);
		// queryForJava(sqdb);
		// queryForSQL(sqdb);
		/**
		 * --------------------------------------------------
		 */
		// groupByUsingSQL(sqdb);
		// groupByUsingJava(sqdb);

		/**
		 * -------------------------------------------------------
		 */
		
		avgUsingSQL(sqdb);
		avgUsingJava(sqdb);
		avgUsingQuery(sqdb);
		

		super.setUp();
	}

	// avg using sql
	public void avgUsingSQL(SQLiteDatabase sqdb) {
		long start = System.currentTimeMillis();
		String colName = "AVG(" + SCORE + ")";

		String sql = SQLiteQueryBuilder.buildQueryString(false, TABLE_NAME,
				new String[] { colName }, null, null, null, null, null);

		Cursor c = sqdb.rawQuery(sql, null);
		while (c.moveToNext()) {
			float avg = c.getFloat(c.getColumnIndex(colName));
			Log.d("avg", String.valueOf(avg));
		}
		Log.d("time", String.valueOf(System.currentTimeMillis() - start)
				+ " milliseconds");
		c.close();
	}

	public void avgUsingQuery(SQLiteDatabase sqdb) {
		long start = System.currentTimeMillis();
		String colName = "AVG(" + SCORE + ")";

		Cursor c = sqdb.query(TABLE_NAME, new String[] { colName }, null, null,
				null, null, null);
		while (c.moveToNext()) {
			float avg = c.getFloat(c.getColumnIndex(colName));
			Log.d("avg", String.valueOf(avg));
		}
		Log.d("time", String.valueOf(System.currentTimeMillis() - start)
				+ " milliseconds");
		c.close();
	}

	// avg using java
	public void avgUsingJava(SQLiteDatabase sqdb) {
		long start = System.currentTimeMillis();
		String sql = SQLiteQueryBuilder.buildQueryString(false, TABLE_NAME,
				new String[] { SCORE }, null, null, null, null, null);
		Cursor c = sqdb.rawQuery(sql, null);
		int count = 0;
		long sum = 0;
		while (c.moveToNext()) {
			int score = c.getInt(c.getColumnIndex(SCORE));
			sum += score;
			count++;
		}
		Log.d("avg", String.valueOf(((float) sum / count)));

		Log.d("time", String.valueOf(System.currentTimeMillis() - start)
				+ " milliseconds");
		c.close();
	}

	// group by using sql
	public void groupByUsingSQL(SQLiteDatabase sqdb) {
		long start = System.nanoTime();
		String colName = "COUNT(" + NAME + ")";
		String sql = SQLiteQueryBuilder.buildQueryString(false, TABLE_NAME,
				new String[] { NAME, colName }, null, NAME, null, null, null);
		Cursor c = sqdb.rawQuery(sql, null);

		while (c.moveToNext()) {
			String name = c.getString(c.getColumnIndex(NAME));
			int count = c.getInt(c.getColumnIndex(colName));
			// Log.d("group by", NAME + ": " + name + "has count " + count);
		}
		Log.d("time", (System.nanoTime() - start) / 1000000 + " MILLISECONDS");
		c.close();
	}

	// group by java
	public void groupByUsingJava(SQLiteDatabase sqdb) {
		long start = System.nanoTime();
		String sql = SQLiteQueryBuilder.buildQueryString(false, TABLE_NAME,
				new String[] { NAME }, null, null, null, null, null);
		Map<String, Integer> map = new HashMap<String, Integer>();
		Cursor c = sqdb.rawQuery(sql, null);
		while (c.moveToNext()) {
			String name = c.getString(c.getColumnIndex(NAME));
			if (map.containsKey(name)) {
				int curValue = map.get(name);
				map.put(name, curValue + 1);

			} else {
				map.put(name, 1);
			}
		}
		for (String key : map.keySet()) {
			// Log.d("group by", NAME + ": " + key + " has count " +
			// map.get(key));
		}
		Log.d("time", String.valueOf((System.nanoTime() - start) / 1000000)
				+ " MILLISECONDS");
		c.close();
	}

	// / SQL OPTIMIZED
	public void queryForSQL(SQLiteDatabase sqdb) {
		long start = System.nanoTime();
		String query = SQLiteQueryBuilder.buildQueryString(false, TABLE_NAME,
				new String[] { NAME }, SCORE + "< 500", null, null, null, null);
		Cursor c = sqdb.rawQuery(query, null);

		int count = 0;
		while (c.moveToNext()) {
			String name = c.getString(c.getColumnIndex(NAME));
			count++;
		}
		Log.d("count", String.valueOf(count));
		Log.d("time", String.valueOf((System.nanoTime() - start) / 1000000));
		c.close();
	}

	// Java optimized
	public void queryForJava(SQLiteDatabase sqdb) {
		long start = System.nanoTime();
		String query = SQLiteQueryBuilder.buildQueryString(false, TABLE_NAME,
				new String[] { NAME, SCORE }, null, null, null, null, null);
		Cursor c = sqdb.rawQuery(query, null);

		int count = 0;
		while (c.moveToNext()) {
			String name = c.getString(c.getColumnIndex(NAME));
			int score = c.getInt(c.getColumnIndex(SCORE));
			if (score < 500) {
				count++;
			}
		}
		Log.d("count", String.valueOf(count));
		Log.d("time", String.valueOf((System.nanoTime() - start) / 1000000));
		c.close();
	}

	public void insertPerformanceForSQL(SQLiteDatabase sqdb) {
		long start = System.nanoTime();
		for (int i = 0; i < 1000; i++) {

			String sql = "INSERT INTO " + TABLE_NAME + "(" + NAME + "," + SCORE
					+ ")" + " VALUES('" + i + "', '" + i + "');";
			sqdb.execSQL(sql);

		}
		Log.d("---time---", (System.nanoTime() - start) / 1000000
				+ "  MILLISECONDS");

	}

	public void insertPerformanceForWrapper(SQLiteDatabase sqdb) {
		SQLiteDatabase sqdb2 = sqh.getWritableDatabase();
		long start = System.nanoTime();
		for (int i = 1000; i < 2000; i++) {
			ContentValues cv = new ContentValues();
			cv.put("name", i);
			cv.put("score", i);
			sqdb2.insert(TABLE_NAME, NAME, cv);
		}
		Log.d("---time---", (System.nanoTime() - start) / 1000000
				+ "  MILLISECONDS");
	}

	@Override
	protected void tearDown() throws Exception {
		// TODO Auto-generated method stub
		super.tearDown();
	}

}
